﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Odbc;
using System.Data;
using Dapper;
using System.Dynamic;
using System.Text.RegularExpressions;
using _180FrameWork.DataBaseHelper.Entities;

namespace _180FrameWork.DataBaseHelper
{
    public abstract class DapperHelper : IDisposable
    {      
        protected Database _db;

        protected DbConnection _Conn;

        public DbConnection Conn
        {
            get
            {
                if (_Conn == null)
                    _Conn = this._db.CreateConnection();
                return this._Conn;
            }
        }

        #region ORM相关
        protected abstract string ParamChar { get; }

        private IDictionary<RuntimeTypeHandle, DataBaseTableSchedule> _catchList;

        private IDictionary<RuntimeTypeHandle, DataBaseTableSchedule> catchList
        {
            get
            {
                if (this._catchList == null)
                {
                    this._catchList = new Dictionary<RuntimeTypeHandle, DataBaseTableSchedule>();
                }
                return this._catchList;
            }
        }

        object locker = new object();

        /// <summary>
        /// 获取结构
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private DataBaseTableSchedule getDataBaseTableSchedule<T>()
        {
            lock (locker)
            {
                var type = typeof(T);
                DataBaseTableSchedule schedule = null;
                if (this.catchList.ContainsKey(type.TypeHandle))
                {
                    schedule = this.catchList[type.TypeHandle];
                }
                else
                {
                    schedule = new DataBaseTableSchedule(type);
                    this.catchList.Add(type.TypeHandle, schedule);
                }
                return schedule;
            }
        }

        /// <summary>
        /// 获取查找SQL语句
        /// </summary>
        /// <param name="Schedule"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        private string GetSelectSQL<T>()
        {
            var schedule = getDataBaseTableSchedule<T>();
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendFormat("select ");
            foreach (var column in schedule.ColumnNoPrimaryKeyList)
            {
                if (column.ColumnName == column.PropertieName)
                {
                    strSQL.AppendFormat("{0},", column.ColumnName);
                }
                else
                {
                    strSQL.AppendFormat("{0} as \"{1}\",", column.ColumnName, column.PropertieName);
                }
            }
            strSQL.Remove(strSQL.Length - 1, 1);
            strSQL.AppendFormat(" from {0} where 1=1", schedule.TableName);
            //根据表名获取主键
            foreach (var key in schedule.PrimaryKeyList)
            {
                strSQL.AppendFormat(" and {0}=" + ParamChar + "{1}", key.ColumnName, key.PropertieName);
            }
            return strSQL.ToString();
        }
        /// <summary>
        /// 获取插入SQL语句
        /// </summary>
        /// <param name="Schedule"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        private string GetInsertSQL<T>()
        {
            var schedule = getDataBaseTableSchedule<T>();
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendFormat("insert into {0}(", schedule.TableName);
            foreach (var item in schedule.ColumnList)
            {
                strSQL.Append(item.ColumnName + ",");
            }
            strSQL.Remove(strSQL.Length - 1, 1);
            strSQL.Append(")values(");
            foreach (var item in schedule.ColumnList)
            {
                strSQL.Append(this.ParamChar + item.PropertieName + ",");
            }
            strSQL.Remove(strSQL.Length - 1, 1);
            strSQL.Append(")");
            return strSQL.ToString();
        }

        /// <summary>
        /// 获取更新SQL语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="data"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        private string GetUpdateSQL<T>(object data)
        {
            var pi = data.GetType().GetProperties();

            StringBuilder strSQL = new StringBuilder();

            var schedule = getDataBaseTableSchedule<T>();
            if (schedule.PrimaryKeyList.Count > 0)
            {
                strSQL.AppendFormat("update {0} set ", schedule.TableName);

                foreach (var p in pi)
                {
                    object val = p.GetValue(data, null);
                    if (val != null)
                    {
                        foreach (var item in schedule.ColumnNoPrimaryKeyList)
                        {
                            if (item.PropertieName == p.Name)
                            {
                                strSQL.Append(item.ColumnName + "=" + this.ParamChar + item.PropertieName + ",");
                            }
                        }
                    }
                }

                strSQL.Remove(strSQL.Length - 1, 1);
                strSQL.Append(" where 1=1 ");
                foreach (var item in schedule.PrimaryKeyList)
                {
                    strSQL.Append(" and " + item.ColumnName + "=" + this.ParamChar + item.PropertieName + "");
                }
                return strSQL.ToString();
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获取删除SQL语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="data"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        private string GetDeleteSQL<T>()
        {
            StringBuilder strSQL = new StringBuilder();
            var schedule = getDataBaseTableSchedule<T>();
            if (schedule.PrimaryKeyList.Count > 0)
            {
                strSQL.AppendFormat("delete from {0} where 1=1 ", schedule.TableName);
                foreach (var item in schedule.PrimaryKeyList)
                {
                    strSQL.Append(" and " + item.ColumnName + "=" + this.ParamChar + item.ColumnName + "");
                }
                return strSQL.ToString();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取分页语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="orderby"></param>
        /// <returns></returns>
        protected abstract string GetPageSQL(string sql, string orderby);

        /// <summary>
        /// 获取
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public T Get<T>(object param, IDbTransaction transaction = null)
        {
            return this.Conn.Query<T>(this.GetSelectSQL<T>(), param, transaction).FirstOrDefault();
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool Insert<T>( object data, IDbTransaction transaction = null)
        {
            var sql = this.GetInsertSQL<T>();
            int i = this.Conn.Execute(sql, data, transaction);
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool Update<T>(object data, IDbTransaction transaction = null)
        {
            int i = this.Conn.Execute(this.GetUpdateSQL<T>(data), data, transaction);
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool Delete<T>(object data, IDbTransaction transaction = null)
        {
            int i = this.Conn.Execute(this.GetDeleteSQL<T>(), data, transaction);
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public   PageData<T> Page<T>(string sql, IDictionary<string, object> param, IDbTransaction transaction = null)
        {
 
            int page = 1;
            int rows = 20;
            string sort = "";
            string order = "";
            int total = 0;
            string SUM = "";
            foreach (KeyValuePair<string, object> item in param)
            {
                switch (item.Key)
                {
                    case "page":
                        page = Convert.ToInt32(item.Value);
                        break;
                    case "rows":
                        rows = Convert.ToInt32(item.Value);
                        break;
                    case "sort":
                        sort = Convert.ToString(item.Value);
                        break;
                    case "order":
                        order = Convert.ToString(item.Value);
                        break;
                        //default:
                        //    dp.Add(item.Key, item.Value);
                        //    break;
                }
            }

            var p = new PageData<T>();
            if (total != 0)
            {
                p.total = total;
            }
            else
            {
                string strTotalSql = "select count(*) from(" + sql + ") t";
                p.total = this.Conn.ExecuteScalar<int>(strTotalSql, param);
                if (p.total == 0)
                    p.total = 1;
            }
            p.totalPages = (p.total / rows) + (p.total % rows > 0 ? 1 : 0);
            //汇总
            if (SUM != "")
            {
                string strSumSql = "select " + SUM + " from(" + sql + ") t";
                p.footer = this.Conn.Query<T>(strSumSql, param).ToList();
            }
            //排序
            string orderby = "";
            if (sort != "")
            {
                orderby = " order by " + sort;
                if (order != "")
                {
                    orderby += " " + order;
                }
            }
            //获取分页数据
            int beginrow = rows * (page - 1);
            int endrow = (rows + 1) * page;
            param.Add("beginrow", beginrow);
            param.Add("endrow", endrow);
            string strDataSql = this.GetPageSQL(sql, orderby);
            p.rows = this.Conn.Query<T>(strDataSql, param).ToList();
            return p;
        }
        #endregion

        //public DapperHelper()
        //{
        //    //_db = DatabaseFactory.CreateDatabase();
        //}

        //public DapperHelper(string DBName)
        //{
        //    if (DBName.Trim() == "")
        //        _db = DatabaseFactory.CreateDatabase();
        //    else
        //        _db = DatabaseFactory.CreateDatabase(DBName.Trim());
        //}

        public void Dispose()
        {
            if (_Conn != null && _Conn.State == ConnectionState.Open)
            {
                _Conn.Close();
            }
            if (_Conn != null)
            {
                _Conn.Dispose();
            }
        }

        
        
    }
}
